--Danh Muc------------
create table tbDanhMuc
(
	MaDM int identity(1,1) not null,
	TenDanhMuc nvarchar(50) not null
	primary key (MaDM)	
)

--Doanh Nghiep---------------------
create table tbDoanhNghiep
(
	MaDN int identity(1,1) not null,
	TenDN nvarchar(100),
	DiaChi nvarchar(100),
	DienThoai nvarchar(15),
	Email nvarchar(50),
	TaiKhoan nvarchar(15),
	MatKhau nvarchar(15)
	primary key(MaDN)
)
--------Tinh Trang----------------
create table tbTinhTrang
(
	MaTT int identity(1,1) not null,
	TenTinhTrang nvarchar(50)
	primary key(MaTT)
)
----Nha San Xuat----------------
create table tbNhaSX
(
	MaNSX int identity(1,1) not null,
	TenNSX nvarchar(50),
	DiaChi nvarchar(100),
	DienThoai nvarchar(15),
	primary key(MaNSX)
)

--------------San pham-----------
create table tbSanPham
(
	MaSP int identity(1,1) not null,
	TenSP nvarchar(100),
	DanhMuc int,
	DoanhNghiep int,
	NhaSX int,
	GiaGoc float,
	GiaBan float,
	DiaDiem int,
	NgayBatDau Datetime,
	NgayHetHan Datetime,
	SoLuongBan int,
	SoLuongToiDa int,
	MoTa ntext,
	QuyDinhSuDung ntext,
	ChietKhau float,
	TinhTrang int,	
	primary key(MaSP)
)
alter table tbSanPham
add constraint fk_SP_TT foreign key(TinhTrang) references tbTinhTrang(MaTT)
alter table tbSanPham
add constraint fk_SP_DM foreign key(DanhMuc) references tbDanhMuc(MaDM)
alter table tbSanPham
add constraint fk_SP_NSX foreign key(NhaSX) references tbNhaSX(MaNSX)
alter table tbSanPham
add constraint fk_SP_DN foreign key(DoanhNghiep) references tbDoanhNghiep(MaDN)
alter table tbSanPham
add constraint fk_SP_DD foreign key(DiaDiem) references tbDiaDiem(MaDD)
alter table tbSanPham
add constraint fk_SP_PTTT foreign key(PhuongThucThanhToan) references tbPhuongThucThanhToan(MaPTTT)
---------------Hinh anh----------
create table tbHinhAnh
(
	MaHA int identity(1,1) not null,
	HinhAnh nvarchar(250),
	MaSP int
	primary key(MaHA)
)
alter table tbHinhAnh
add constraint fk_HA_SP foreign key(MaSP) references tbSanPham(MaSP)
--------------Chi Tiet Hinh Anh------------
----Bo~-----
--KHach Hang------------
create table tbKhachHang
(
	MaKH int identity(1,1) not null,
	HoTen nvarchar(50),
	GioiTinh nchar(5),
	NgaySinh Datetime,
	Email nvarchar(250),
	TaiKhoan nvarchar(250),
	MatKhau nvarchar(250),
	DiaChi ntext,
	DienThoai nvarchar(250),
	SoTKNH nvarchar(250)
	primary key(MaKH)
)
----------------Binh lUan---------------------
create table tbBinhLuan
(
	MaBL int identity(1,1) not null,
	KhachHang int not null,
	SanPham int not null,
	NoiDung ntext,
	NgayGui nvarchar(225),
	primary key(MaBL)
)
alter table tbBinhLuan
add constraint fk_BL_SP foreign key(SanPham) references tbSanPham(MaSP)
alter table tbBinhLuan
add constraint fk_BL_KH foreign key(KhachHang) references tbKhachHang(MaKH)
-----------Tinh Trang Thanh Toan-------------
create table tbTinhTrangThanhToan
(
	MaTTTT int identity(1,1),
	TenTTTT nvarchar(50)
	primary key(MaTTTT)
)
---------------Phuong Thuc Thanh Toan------------
create table tbPhuongThucThanhToan
(
	MaPTTT int identity(1,1),
	TenPTTT nvarchar(50)
	primary key(MaPTTT)
)
----------Tinh Trang  Giao hang-------------
create table tbTinhTrangGH
(
	MaTTGH int identity(1,1),
	TenTTGH nvarchar(50),
	primary key(MaTTGH)
)
-----------------Nhan Vien------------
create table tbNhanVien
(
	MaNV int identity(1,1),
	TaiKhoan nvarchar(100),
	MatKhau nvarchar(100),
	TenNV nvarchar(100),
	DiaChi nvarchar(100),
	DienThoai nvarchar(15)	
	 Primary key(MaNV)
)
-----------Don Hang--------------
create table tbDonHang
(	
	MaDH int identity(1,1),
	KhachHang int,
	NgayDatHang datetime,
	NgayGiaoHang datetime,
	TriGia int,
	TinhTrangGH int,
	PhuongThucThanhToan int,
	TinhTrangThanhToan int,
	NhanVienGiaoHang int,
	DiaChiGH int,
	Constraint pk_tbDonHang primary key(MaDH)
)
alter table tbDonHang
add constraint fk_DH_KH foreign key(KhachHang) references tbKhachHang(MaKH)
alter table tbDonHang
add constraint fk_DH_TTTT foreign key(TinhTrangThanhToan) references tbTinhTrangThanhToan(MaTTTT)
alter table tbDonHang
add constraint fk_DH_PTTT foreign key(PhuongThucThanhToan) references tbPhuongThucThanhToan(MaPTTT)
alter table tbDonHang
add constraint fk_DH_TTGH foreign key(TinhTrangGH) references tbTinhTrangGH(MaTTGH)
alter table tbDonHang
add constraint fk_DH_NV foreign key(NhanVienGiaoHang) references tbNhanVien(MaNV)
-------------Chi Tiet Don hang---------------
create table tbCTDonHang
(
	MaCTDH int identity(1,1) not null,
	MaDH int not null,
	SanPham int not null,
	SoLuong int,
	DonGia int,
	primary key(MaCTDH)
)
alter table tbCTDonHang
add constraint fk_CTDH_SP foreign key(SanPham) references tbSanPham(MaSP)
alter table tbCTDonHang
add constraint fk_CTDH_DH foreign key(MaDH) references tbDonHang(MaDH)



-------------------------------Cua Hang-----------------------
create table tbCuaHang
(
	MaCH int identity(1,1),
	TenCH nvarchar(100),
	SanPham int,
	primary key(MaCH),
	
)
alter table tbCuaHang
add constraint fk_CH_SP foreign key(SanPham) references tbSanPham(MaSP)

-------------Tra Loi--------------
create table tbTraLoi
(
	MaTL int identity(1,1) not null,
	BinhLuan int not null,
	KhachHang int not null,
	TraLoi ntext,
	NgayTraLoi nvarchar(225),
	primary key(MaTL),
	foreign key(BinhLuan) references tbBinhLuan(MaBL),
	foreign key(KhachHang) references tbKhachHang(MaKH)
)


------------Dia Diem-------------
create table tbDiaDiem
(
	MaDD int identity(1,1),
	TenDD nvarchar(100),
	DiaChi nvarchar(100),
	DienThoai nvarchar(15),
	BanDo nvarchar(225)
	primary key(MaDD)
)

-------------------------------------Tinh Trang Deal--------------------
create table tbTinhTrangDeal
(
	MaTTDEAL int identity(1,1) not null,
	TenTTDEAL nvarchar(50)
	primary key(MaTTDEAL)
)
----------ban Deal----------
create table tbDeal
(
	MaDeal int identity(1,1) not null,
	SanPham int not null,
	TinhTrang int not null
	primary key (MaDeal)
	
)
alter table tbDeal
add constraint fk_Deal_SP foreign key(SanPham) references tbSanPham(MaSP)
alter table tbDeal
add constraint fk_Deal_TTDEAL foreign key(TinhTrang) references tbTinhTrangDeal(MaTTDEAL)








---------------------------------------------Procedure---------------------------------------











----------------------------procedure Danh Muc-----------------------------------------------------
create procedure sp_tbDanhMuc_insert

	@TenDanhMuc nvarchar(50)
as
	Begin
		insert into tbDanhMuc 
		values(@TenDanhMuc)

	End

-------------------------------
create procedure sp_tbDanhMuc_update
	@MaDM int, 
	@TenDanhMuc nvarchar(50)
as
	Begin
update tbDanhMuc 
set TenDanhMuc=@TenDanhMuc
 where MaDM=@MaDM
End
------------------
create procedure sp_tbDanhMuc_delete
@MaDM int 
as
delete from tbDanhMuc
		Where MaDM=@MaDM
	

-----------------
create procedure sp_tbDanhMuc_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
	Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaDM nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaDM]) select [MaDM] from tbDanhMuc' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaDM, 
	S.TenDanhMuc 
	From #Temp As T JOIN tbDanhMuc as S on T.MaDM=S.MaDM
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
-----------------------------procedure Doanh Nghiep-----------------
create procedure sp_tbDoanhNghiep_insert
	@TenDN nvarchar(100),
	@DiaChi nvarchar(100),
	@DienThoai nvarchar(15),
	@Email nvarchar(50),
	@TaiKhoan nvarchar(15),
	@MatKhau nvarchar(15)
as
	Begin
insert into tbDoanhNghiep values
(
	@TenDN,
	@DiaChi,
	@DienThoai,
	@Email,
	@TaiKhoan,
	@MatKhau
)
	End
-------------------------------
create procedure sp_tbDoanhNghiep_update
	@MaDN int, 
	@TenDN nvarchar(100),
	@DiaChi nvarchar(100),
	@DienThoai nvarchar(15),
	@Email nvarchar(50),
	@TaiKhoan nvarchar(15),
	@MatKhau nvarchar(15)
as
	Begin
update tbDoanhNghiep set
	TenDN=@TenDN,
	DiaChi=@DiaChi,
	DienThoai=@DienThoai,
	Email=@Email,
	TaiKhoan=@TaiKhoan,
	MatKhau=@MatKhau
 where MaDN=@MaDN
End
------------------
create procedure sp_tbDoanhNghiep_delete
@MaDN int 
as
Delete from tbDoanhNghiep
	Where MaDN=@MaDN
	

-----------------
create procedure sp_tbDoanhNghiep_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaDN nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaDN]) select [MaDN] from tbDoanhNghiep' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaDN, 
	S.TenDN,
	S.DiaChi,
	S.DienThoai,
	S.Email,
	S.TaiKhoan,
	S.MatKhau
	From #Temp As T JOIN tbDoanhNghiep as S on T.MaDN=S.MaDN
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
-------------------------procedure TinhTrang----------------------
create procedure sp_tbTinhTrang_insert 
	@TenTinhTrang nvarchar(50)
as
insert into tbTinhTrang values
(
	@TenTinhTrang 
)
---------
create procedure sp_tbTinhTrang_update
	@MaTT int, 
	@TenTinhTrang nvarchar(50)
as
update tbTinhTrang set
	TenTinhTrang=@TenTinhTrang
 where (MaTT=@MaTT)
------------------
create procedure sp_tbTinhTrang_delete
@MaTT int
as
delete from tbTinhTrang where( MaTT=@MaTT)

-----------------
create procedure sp_tbTinhTrang_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaTT nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaTT]) select [MaTT] from tbTinhTrang' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaTT, 
	S.TenTinhTrang
	From #Temp As T JOIN tbTinhTrang as S on T.MaTT=S.MaTT
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
----------------------------------------------------------procedure Nha San Xuan-------------------------------------
create procedure sp_tbNhaSX_insert
	@TenNSX nvarchar(50),
	@DiaChi nvarchar(100),
	@DienThoai nvarchar(15)
as
insert into tbNhaSX values
(
	@TenNSX,
	@DiaChi,
	@DienThoai
)
-------------------------------
create procedure sp_tbNhaSX_update
	@MaNSX int, 
	@TenNSX nvarchar(50),
	@DiaChi nvarchar(100),
	@DienThoai nvarchar(15)
as
update tbNhaSX set
	TenNSX=@TenNSX,
	DiaChi=@DiaChi,
	DienThoai=@DienThoai
 where MaNSX=@MaNSX
------------------
create procedure sp_tbNhaSX_delete
@MaNSX int 
as


	delete from tbNhaSX where (MaNSX=@MaNSX)

-----------------
create procedure sp_tbNhaSX_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
create table #Temp (
	IDtemp int identity(1,1),
	MaNSX nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaNSX]) select [MaNSX] from tbNhaSX' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaNSX, 
	S.TenNSX,
	S.DiaChi,
	S.DienThoai
	From #Temp As T JOIN tbNhaSX as S on T.MaNSX=S.MaNSX
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
--------------------------procedure san Pham-----------------------
create procedure sp_tbSanPham_insert
	@TenSP nvarchar(100), 
	@DanhMuc int,
	@DoanhNghiep int,
	@NhaSX int,
	@GiaGoc float,
	@GiaBan float,
	@DiaDiem int ,
	@NgayBatDau Datetime,
	@NgayHetHan Datetime,
	@SoLuongBan int,
	@SoLuongToiDa int,
	@MoTa ntext,
	@QuyDinhSuDung ntext,
	@ChietKhau float,
	@TinhTrang int
as
insert into tbSanPham
(
	TenSP,
	DanhMuc, 
	DoanhNghiep,
	NhaSX,
	GiaGoc,
	GiaBan,
	DiaDiem,
	NgayBatDau,
	NgayHetHan,
	SoLuongBan,
	SoLuongToiDa,
	MoTa,
	QuyDinhSuDung,
	ChietKhau,
	TinhTrang
)
values
(
	@TenSP,
	@DanhMuc, 
	@DoanhNghiep,
	@NhaSX,
	@GiaGoc,
	@GiaBan,
	@DiaDiem,
	@NgayBatDau,
	@NgayHetHan,
	@SoLuongBan,
	@SoLuongToiDa,
	@MoTa,
	@QuyDinhSuDung,
	@ChietKhau,
	@TinhTrang
)
---------
create procedure sp_tbSanPham_update
	@MaSP int,
	@TenSP nvarchar(100), 
	@DanhMuc int,
	@DoanhNghiep int,
	@NhaSX int,
	@GiaGoc float,
	@GiaBan float,
	@DiaDiem int,
	@NgayBatDau Datetime,
	@NgayHetHan Datetime,
	@SoLuongBan int,
	@SoLuongToiDa int,
	@MoTa ntext,
	@QuyDinhSuDung ntext,
	@ChietKhau float,
	@TinhTrang int
as
update tbSanPham set
	TenSP=@TenSP,
	DanhMuc=@DanhMuc,
	DoanhNghiep=@DoanhNghiep,
	NhaSX=@NhaSX,
	GiaGoc=@GiaGoc,
	GiaBan=@GiaBan,
	DiaDiem=@DiaDiem,
	NgayBatDau=@NgayBatDau,
	NgayHetHan=@NgayHetHan,
	SoLuongBan=@SoLuongBan,
	SoLuongToiDa=@SoLuongToiDa,
	MoTa=@MoTa,
	QuyDinhSuDung=@QuyDinhSuDung,
	ChietKhau=@ChietKhau,
	TinhTrang=@TinhTrang
where MaSP=@MaSP
------------------
create procedure sp_tbSanPham_delete
@MaSP int 
as
	Declare @Total int
	select @Total=Count(*) from tbCTDonHang where SanPham=@MaSP
	if(@Total=0)
	begin
		DELETE FROM tbSanPham where MaSP = @MaSP
	end
-----------------
create procedure sp_tbSanPham_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaSP nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaSP]) select [MaSP] from tbSanPham' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaSP, 
	S.TenSP, 
	S.DanhMuc,
	S.DoanhNghiep,
	S.NhaSX,
	S.GiaGoc,
	S.GiaBan,
	S.DiaDiem,
	S.NgayBatDau,
	S.NgayHetHan,
	S.SoLuongBan,
	S.SoLuongToiDa,
	S.MoTa,
	S.QuyDinhSuDung,
	S.ChietKhau,
	S.TinhTrang 
	From #Temp As T JOIN tbSanPham as S on T.MaSP=S.MaSP
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
-----------------------------------procedure Hinh Anh-------------------------
create procedure sp_tbHinhAnh_insert
	@HinhAnh nvarchar(250),
	@MaSP int
as
insert into tbHinhAnh 
(
	HinhAnh,
	MaSP
)
values
(
	@HinhAnh,
	@MaSP
)
-------------------------------
create procedure sp_tbHinhAnh_update
	@MaHA int, 
	@HinhAnh nvarchar(250),
	@MaSP int
as
update tbHinhAnh set
	HinhAnh=@HinhAnh,
	MaSP=@MaSP
 where (@MaHA=tbHinhAnh.MaHA)
------------------
create procedure sp_tbHinhAnh_delete
@MaHA int 
as

	delete from tbHinhAnh where MaHA=@MaHA

-----------------
create procedure sp_tbHinhAnh_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaHA nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaHA]) select [MaHA] from tbHinhAnh' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaHA, 
	S.HinhAnh ,
	S.MaSP
	From #Temp As T JOIN tbHinhAnh as S on T.MaHA=S.MaHA
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
---------------------------procedure CT HINH ANH-----------------
-----bo~--------
-------------------------------------procedure Khach Hang--------------------------
create procedure sp_tbKhachHang_insert
	@HoTen nvarchar(50),
	@GioiTinh nchar(5),
	@NgaySinh Datetime,
	@Email nvarchar(250),
	@TaiKhoan nvarchar(250),
	@MatKhau nvarchar(250),
	@DiaChi ntext,
	@DienThoai nvarchar(250),
	@SoTKNH nvarchar(250)
as
insert into tbKhachHang
(
	HoTen,
	GioiTinh,
	NgaySinh,
	Email,
	TaiKhoan,
	MatKhau,
	DiaChi,
	DienThoai,
	SoTKNH
)
	values
(
	@HoTen,
	@GioiTinh,
	@NgaySinh,
	@Email,
	@TaiKhoan,
	@MatKhau,
	@DiaChi,
	@DienThoai,
	@SoTKNH
)
-------------------------------
create procedure sp_tbKhachHang_update
	@MaKH int, 
	@HoTen nvarchar(50),
	@GioiTinh nchar(5),
	@NgaySinh Datetime,
	@Email nvarchar(250),
	@TaiKhoan nvarchar(250),
	@MatKhau nvarchar(250),
	@DiaChi ntext,
	@DienThoai nvarchar(250),
	@SoTKNH nvarchar(250)
as
update tbKhachHang set
	HoTen=@HoTen,
	GioiTinh=@GioiTinh,
	NgaySinh=@NgaySinh,
	Email=@Email,
	TaiKhoan=@TaiKhoan,
	MatKhau=@MatKhau,
	DiaChi=@DiaChi,
	DienThoai=@DienThoai,
	SoTKNH=@SoTKNH
 where (@MaKH=tbKhachHang.MaKH)
------------------
create procedure sp_tbKhachHang_delete
@MaKH int 
as
	Declare @Total int
	select @Total=count(*) from tbDonHang Where KhachHang=@MaKH
	if(@Total=0)
	begin
	delete from tbKhachHang
	 where 	(MaKH=@MaKH)
	end

-----------------
create procedure sp_tbKhachHang_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaKH nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaKH]) select [MaKH] from tbKhachHang' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaKH, 
	S.HoTen,
	S.GioiTinh,
	S.NgaySinh,
	S.Email,
	S.TaiKhoan,
	S.MatKhau,
	S.DiaChi,
	S.DienThoai,
	S.SoTKNH
	From #Temp As T JOIN tbKhachHang as S on T.MaKH=S.MaKH
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
-----------------------------------------------procedure BinhLuan-------------------------------------------------
create procedure sp_tbBinhLuan_insert
	@KhachHang int ,
	@SanPham int ,
	@NoiDung ntext,
	@NgayGui nvarchar(225)
as
insert into tbBinhLuan
(
	KhachHang, 
	SanPham, 
	NoiDung, 
	NgayGui 
)
values
(
	@KhachHang, 
	@SanPham, 
	@NoiDung, 
	@NgayGui 
)
-----------------
create procedure sp_tbBinhLuan_update
	@MaBL int ,
	@KhachHang int ,
	@SanPham int ,
	@NoiDung ntext,
	@NgayGui nvarchar(225)
as
update tbBinhLuan set
	KhachHang=@KhachHang ,
	SanPham=@SanPham ,
	NoiDung=@NoiDung ,
	Ngaygui=@NgayGui
 where (@MaBL=tbBinhLuan.MaBL)

----------------
create procedure sp_tbBinhLuan_delete
@MaBL int 
as

	delete from tbBinhLuan where ( MaBL=@MaBL)

----------------
create procedure sp_tbBinhLuan_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaBL nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaBL]) select [MaBL] from tbBinhLuan' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaBL, 
	S.KhachHang, 
	S.SanPham, 
	S.NoiDung,
	S.NgayGui
From #Temp As T JOIN tbBinhLuan as S on T.MaBL=S.MaBL
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
----------------------------------procedure Tinh Trang Thanh Toan-----------------------
create procedure sp_tbTinhTrangThanhToan_insert 
	@TenTTTT nvarchar(50)
as
insert into tbTinhTrangThanhToan values
( 
	@TenTTTT
)
---------
create procedure sp_tbTinhTrangThanhToan_update
	@MaTTTT int,
	@TenTTTT nvarchar(50)	
as
update tbTinhTrangThanhToan set
	TenTTTT=@TenTTTT
 where( MaTTTT=@MaTTTT)
------------------
create procedure sp_tbTinhTrangThanhToan_delete
@MaTTTT int 
as


	delete from tbTinhTrangThanhToan where MaTTTT=@MaTTTT

-----------------
create procedure sp_tbTinhTrangThanhToan_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaTTTT nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaTTTT]) select [MaTTTT] from tbTinhTrangThanhToan' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaTTTT, 
	S.TenTTTT
	From #Temp As T JOIN tbTinhTrangThanhToan as S on T.MaTTTT=S.MaTTTT
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
----------------------------------------------procedure phuong thuc thanh toan----------------
create procedure sp_tbPhuongThucThanhToan_insert
	@TenPTTT nvarchar(50) 
as
insert into tbPhuongThucThanhToan values
(
	@TenPTTT 
)
---------
create procedure sp_tbPhuongThucThanhToan_update
	@MaPTTT int,
	@TenPTTT nvarchar(50)
as
update tbPhuongThucThanhToan set
	TenPTTT=@TenPTTT 
 where (MaPTTT=@MaPTTT)
------------------
create procedure sp_tbPhuongThucThanhToan_delete
@MaPTTT int 
as

	delete from tbPhuongThucThanhToan where (MaPTTT=@MaPTTT)

-----------------
create procedure sp_tbPhuongThucThanhToan_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaPTTT nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaPTTT]) select [MaPTTT] from tbPhuongThucThanhToan' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaPTTT, 
	S.TenPTTT
	From #Temp As T JOIN tbPhuongThucThanhToan as S on T.MaPTTT=S.MaPTTT
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
------------------------------procedure Tinh Trang Giao Hang------------------
create procedure sp_tbTinhTrangGH_insert 
	@TenTTGH nvarchar(50)
as
insert into tbTinhTrangGH values
(
	@TenTTGH
)
---------
create procedure sp_tbTinhTrangGH_update
	@MaTTGH int,
	@TenTTGH nvarchar(50)	
as
update tbTinhTrangGH set
	TenTTGH=@TenTTGH
 where (@MaTTGH=tbTinhTrangGH.MaTTGH)
------------------
create procedure sp_tbTinhTrangGH_delete
@MaTTGH int 
as

	delete from tbTinhTrangGH where MaTTGH=@MaTTGH

-----------------
create procedure sp_tbTinhTrangGH_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaTTGH nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaTTGH]) select [MaTTGH] from tbTinhTrangGH' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaTTGH, 
	S.TenTTGH
	From #Temp As T JOIN tbTinhTrangGH as S on T.MaTTGH=S.MaTTGH
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
------------------------------------ procedure Nhan Vien--------------------------------
create procedure sp_tbNhanVien_insert
	@TaiKhoan nvarchar(100),
	@MatKhau nvarchar(100),
	@TenNV nvarchar(100),
	@DiaChi nvarchar(100),
	@DienThoai nvarchar(15)
as
insert into tbNhanVien 
(
	TaiKhoan,
	MatKhau,
	TenNV,
	DiaChi,
	DienThoai
)
values
(
	@TaiKhoan,
	@MatKhau,
	@TenNV,
	@DiaChi,
	@DienThoai
)
-------------------------------
create procedure sp_tbNhanVien_update
	@MaNV int, 
	@TaiKhoan nvarchar(100),
	@MatKhau nvarchar(100),
	@TenNV nvarchar(100),
	@DiaChi nvarchar(100),
	@DienThoai nvarchar(15)
as
update tbNhanVien set
	TaiKhoan=@TaiKhoan,
	MatKhau=@MatKhau,
	TenNV=@TenNV,
	DiaChi=@DiaChi,
	DienThoai=@DienThoai
 where (@MaNV=tbNhanVien.MaNV)
------------------
create procedure sp_tbNhanVien_delete
@MaNV int 
as
delete from tbNhanVien
	 where 	(MaNV=@MaNV)
-----------------
create procedure sp_tbNhanVien_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaNV nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaNV]) select [MaNV] from tbNhanVien' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaNV, 
	S.TaiKhoan,
	S.MatKhau,
	S.TenNV,
	S.DiaChi,
	S.DienThoai
	From #Temp As T JOIN tbNhanVien as S on T.MaNV=S.MaNV
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
--------------------------------------procedure Don Hang---------------------
create procedure sp_tbDonHang_insert
	@KhachHang int,
	@NgayDatHang datetime,
	@NgayGiaoHang datetime,
	@TriGia int,
	@TinhTrangGH int,
	@PhuongThucThanhToan int,
	@TinhTrangThanhToan int,
	@NhanVienGiaoHang int,
	@DiaChiGH int
as
insert into tbDonHang values
(
	@KhachHang,
	@NgayDatHang,
	@NgayGiaoHang,
	@TriGia,
	@TinhTrangGH,
	@PhuongThucThanhToan,
	@TinhTrangThanhToan,
	@NhanVienGiaoHang,
	@DiaChiGH
)
-------------------------------
create procedure sp_tbDonHang_update
	@MaDH int,
	@KhachHang int, 
	@NgayDatHang datetime,
	@NgayGiaoHang datetime,
	@TriGia int,
	@TinhTrangGH int,
	@PhuongThucThanhToan int,
	@TinhTrangThanhToan int,
	@NhanVienGiaoHang int,
	@DiaChiGH int
as
update tbDonHang set
	KhachHang=@KhachHang,
	NgayDatHang=@NgayDatHang,
	NgayGiaoHang=@NgayGiaoHang,
	TriGia=@TriGia,
	TinhTrangGH=@TinhTrangGH,
	PhuongThucThanhToan=@PhuongThucThanhToan,
	TinhTrangThanhToan=@TinhTrangThanhToan,
	DiaChiGH=@DiaChiGH
 where (@MaDH=tbDonHang.MaDH)
------------------
create procedure sp_tbDonHang_delete
@MaDH int 
as
Declare @Total int
select @Total=Count(*) from tbCTDonHang where MaDH=@MaDH
if(@Total=0)
begin
	DELETE FROM tbDonHang where MaDH = @MaDH
end

-----------------
create procedure sp_tbDonHang_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaDH nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaDH]) select [MaDH] from tbDonHang' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaDH, 
	S.KhachHang,
	S.NgayDatHang,
	S.NgayGiaoHang,
	S.TriGia,
	S.TinhTrangGH,
	S.PhuongThucThanhToan,
	S.TinhTrangThanhToan,
	S.NhanVienGiaoHang,
	S.DiaChiGH
	From #Temp As T JOIN tbDonHang as S on T.MaDH=S.MaDH
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
---------------------------------------------------procedure CTDONHANG---------------
create procedure sp_tbCTDonHang_insert
	@MaDH int,
	@SanPham int,
	@SoLuong int,
	@DonGia int
as
insert into tbCTDonHang values
(
	@MaDH,
	@SanPham,
	@SoLuong,
	@DonGia 
)
-------------------------------
create procedure sp_tbCTDonHang_update
	@MaCTDH int,
	@MaDH int, 
	@SanPham int,
	@SoLuong int,
	@DonGia int	
as
update tbCTDonHang set
	MaDH=@MaDH,
	SanPham=@SanPham,
	SoLuong=@SoLuong,
	DonGia=@DonGia 
 where @MaCTDH=tbCTDonHang.MaCTDH
------------------
create procedure sp_tbCTDonHang_delete
@MaCTDH int

as
Delete from tbCTDonHang
Where 	(MaCTDH=@MaCTDH) 
-----------------
create procedure sp_tbCTDonHang_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaCTDH nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaCTDH]) select [MaCTDH] from tbCTDonHang' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaCTDH,
	S.MaDH, 
	S.SanPham, 
	S.SoLuong,
	S.DonGia 
	From #Temp As T JOIN tbCTDonHang as S on T.MaCTDH=S.MaCTDH
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
---------------------procedure CuaHang----------------
create procedure sp_tbCuaHang_insert
	@TenCH nvarchar(100),
	@SanPham int
as
insert into tbCuaHang values
(
	@TenCH, 
	@SanPham
)
---------
create procedure sp_tbCuaHang_update
	@MaCH int, 
	@TenCH nvarchar(100),
	@SanPham int	
as
update tbCuaHang set
	TenCH=@TenCH ,
	SanPham=@SanPham 
 where(MaCH=@MaCH)
------------------
create procedure sp_tbCuaHang_delete
@MaCH int 
as


	delete from tbCuaHang where (MaCH=@MaCH)

-----------------
create procedure sp_tbCuaHang_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaCH nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaCH]) select [MaCH] from tbCuaHang' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaCH, 
	S.TenCH, 
	S.SanPham 
	From #Temp As T JOIN tbCuaHang as S on T.MaCH=S.MaCH
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
----------------------------procedure Tra Loi------------------
create procedure sp_tbTraLoi_insert
	@BinhLuan int,
	@KhachHang int,
	@TraLoi ntext,
	@NgayTraLoi nvarchar(225)
as
insert into tbTraLoi 
(
	BinhLuan, 
	KhachHang,
	TraLoi,
	NgayTraLoi
)
values
(
	@BinhLuan, 
	@KhachHang,
	@TraLoi,
	@NgayTraLoi
)
---------
create procedure sp_tbTraLoi_update
	@MaTL int,
	@BinhLuan int,
	@KhachHang int,
	@TraLoi ntext,
	@NgayTraLoi nvarchar(225)
as
update tbTraLoi set
	BinhLuan=@BinhLuan,
	KhachHang=@KhachHang,
	TraLoi=@TraLoi,
	NgayTraLoi=@NgayTraLoi
 where (@MaTL=tbTraLoi.MaTL)
------------------
create procedure sp_tbTraLoi_delete
@MaTL int 
as
delete from dbo.[tbTraLoi]
	 where 	(@MaTL=tbTraLoi.MaTL)
-----------------
create procedure sp_tbTraLoi_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaTL nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaTL]) select [MaTL] from tbTraLoi' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaTL, 
	S.BinhLuan,
	S.KhachHang,
	S.TraLoi,
	S.NgayTraLoi
	From #Temp As T JOIN tbTraLoi as S on T.MaTL=S.MaTL
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
------------------------------procedure Dia Diem---------------------
create procedure sp_tbDiaDiem_insert
	@TenDD nvarchar(100),
	@DiaChi nvarchar(100),
	@DienThoai nvarchar(15),
	@BanDo nvarchar(225)
as
insert into tbDiaDiem values
(	
	@TenDD,
	@DiaChi,
	@DienThoai,
	@BanDo
)
-------------------------------
create procedure sp_tbDiaDiem_update
	@MaDD int,
	@TenDD nvarchar(100), 
	@DiaChi nvarchar(100),
	@DienThoai nvarchar(15),
	@BanDo nvarchar(225)
as
update tbDiaDiem set
	TenDD=@TenDD,
	DiaChi=@DiaChi,
	DienThoai=@DienThoai,
	BanDo=@BanDo
 where (MaDD=@MaDD)
------------------
create procedure sp_tbDiaDiem_delete
@MaDD int 
as


	delete from tbDiaDiem where (MaDD=@MaDD)

-----------------
create procedure sp_tbDiaDiem_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaDD nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaDD]) select [MaDD] from tbDiaDiem' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaDD,
	S.TenDD, 
	S.DiaChi,
	S.DienThoai,
	S.BanDo 
	From #Temp As T JOIN tbDiaDiem as S on T.MaDD=S.MaDD
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp

---------------------------------procedure Deal-----------------
create procedure sp_tbDeal_insert

	@SanPham int,
	@TinhTrang int
as
insert into tbDeal values
(

	@SanPham,
	@TinhTrang
)
-------------------------------
create procedure sp_tbDeal_update
	@MaDeal int, 
	@SanPham int,
	@TinhTrang int
as
update tbDeal set

	SanPham=@SanPham,
	TinhTrang=@TinhTrang
 where @MaDeal=tbDeal.MaDeal
------------------
create procedure sp_tbDeal_delete
@MaDeal int 
as

	delete from tbDeal where MaDeal=@MaDeal

-----------------
create procedure sp_tbDeal_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaDeal nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaDeal]) select [MaDeal] from tbDeal' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaDeal, 
	S.SanPham,
	S.TinhTrang 
	From #Temp As T JOIN tbDeal as S on T.MaDeal=S.MaDeal
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp
---------------------------------procedure Tinh Trang Deal----------------
create procedure sp_tbTinhTrangDeal_insert
 
	@TenTTDEAL nvarchar(50)
as
insert into tbTinhTrangDeal

 values(@TenTTDEAL)

---------
create procedure sp_tbTinhTrangDeal_update
	@MaTTDEAL int,
	@TenTTDEAL nvarchar(50)	
as
update tbTinhTrangDeal set
	TenTTDEAL=@TenTTDEAL
 where MaTTDEAL=@MaTTDEAL
------------------
create procedure sp_tbTinhTrangDeal_delete
@MaTTDEAL int 
as

	delete from tbTinhTrangDeal where(MaTTDEAL=@MaTTDEAL)

-----------------
create procedure sp_tbTinhTrangDeal_get
	@Where Nvarchar(255),
	@PageIndex Int,
	@PageSize Int,
	@OrderBy nvarchar(25),
	@OrderDirection nvarchar(4),
	@TotalRecords int output
as
Set Transaction	Isolation	Level	Read	Committed
create table #Temp (
	IDtemp int identity(1,1),
	MaTTDEAL nchar(10)
)
Declare @sql nvarchar(1000)
set @sql='insert into #Temp([MaTTDEAL]) select [MaTTDEAL] from tbTinhTrangDeal' + @Where + ' Order By ' + @OrderBy + ' ' + @OrderDirection
exec (@sql)
select @TotalRecords = Count(*) FROM #Temp
select
	S.MaTTDEAL, 
	S.TenTTDEAL
	From #Temp As T JOIN tbTinhTrangDeal as S on T.MaTTDEAL=S.MaTTDEAL
where
	(@PageIndex = 0)
	OR
	(T.IDTemp>(@PageIndex -1)*@PageSize And
	T.IDTemp<=@PageIndex*@PageSize)
DROP Table #temp



------------------------------procedure Hinh anh dau tien----------

create procedure sp_tbHinhAnh_GetFirstImage
	@MaSP int,
	@MaHA int output
as
	declare @MaFirst int
	set @MaHA=0
select @MaFirst=Min(MaHA)from tbHinhAnh as H where H.MaSP=@MaSP
if(@MaFirst>0)
	begin
		set @MaHA=@MaFirst
	End